
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/30/2010 17:12:19
-- Generated from EDMX file: C:\ASAP\Modelo\Diagrama.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [asap];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_LeccionEjercicio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Ejercicios] DROP CONSTRAINT [FK_LeccionEjercicio];
GO
IF OBJECT_ID(N'[dbo].[FK_LeccionPagina]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Paginas] DROP CONSTRAINT [FK_LeccionPagina];
GO
IF OBJECT_ID(N'[dbo].[FK_LibroLeccion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Lecciones] DROP CONSTRAINT [FK_LibroLeccion];
GO
IF OBJECT_ID(N'[dbo].[FK_LeccionSeccion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Lecciones] DROP CONSTRAINT [FK_LeccionSeccion];
GO
IF OBJECT_ID(N'[dbo].[FK_EjercicioResultado]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Resultados] DROP CONSTRAINT [FK_EjercicioResultado];
GO
IF OBJECT_ID(N'[dbo].[FK_ResultadoTeoria]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Resultados] DROP CONSTRAINT [FK_ResultadoTeoria];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Ejercicios]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Ejercicios];
GO
IF OBJECT_ID(N'[dbo].[Lecciones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Lecciones];
GO
IF OBJECT_ID(N'[dbo].[Libros]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Libros];
GO
IF OBJECT_ID(N'[dbo].[Paginas]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Paginas];
GO
IF OBJECT_ID(N'[dbo].[Secciones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Secciones];
GO
IF OBJECT_ID(N'[dbo].[Resultados]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Resultados];
GO
IF OBJECT_ID(N'[dbo].[Teorias]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Teorias];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Ejercicios'
CREATE TABLE [dbo].[Ejercicios] (
    [Nivel] nvarchar(20)  NOT NULL,
    [Volumen] int  NOT NULL,
    [TipoEjercicio] nvarchar(100)  NOT NULL,
    [Tema] nvarchar(200)  NOT NULL,
    [NumEjercicio] int  NOT NULL,
    [Castellano] nvarchar(2000)  NOT NULL,
    [Ingles] nvarchar(2000)  NOT NULL,
    [Comentario] nvarchar(2000)  NULL,
    [EsVocabulario] bit  NULL
);
GO

-- Creating table 'Lecciones'
CREATE TABLE [dbo].[Lecciones] (
    [Nivel] nvarchar(20)  NOT NULL,
    [Volumen] int  NOT NULL,
    [TipoEjercicio] nvarchar(100)  NOT NULL,
    [Tema] nvarchar(200)  NOT NULL,
    [Audio] varbinary(max)  NOT NULL
);
GO

-- Creating table 'Libros'
CREATE TABLE [dbo].[Libros] (
    [Nivel] nvarchar(20)  NOT NULL,
    [Volumen] int  NOT NULL
);
GO

-- Creating table 'Paginas'
CREATE TABLE [dbo].[Paginas] (
    [Nivel] nvarchar(20)  NOT NULL,
    [Volumen] int  NOT NULL,
    [TipoEjercicio] nvarchar(100)  NOT NULL,
    [Tema] nvarchar(200)  NOT NULL,
    [NumPagina] int  NOT NULL,
    [Imagen] varbinary(max)  NOT NULL
);
GO

-- Creating table 'Secciones'
CREATE TABLE [dbo].[Secciones] (
    [TipoEjercicio] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Resultados'
CREATE TABLE [dbo].[Resultados] (
    [Nivel] nvarchar(20)  NOT NULL,
    [Volumen] int  NOT NULL,
    [TipoEjercicio] nvarchar(100)  NOT NULL,
    [Tema] nvarchar(200)  NOT NULL,
    [NumEjercicio] int  NOT NULL,
    [Fecha] datetime  NOT NULL,
    [Ingles] nvarchar(2000)  NOT NULL,
    [Correcto] bit  NOT NULL,
    [TituloTeoria] nvarchar(100)  NULL
);
GO

-- Creating table 'Teorias'
CREATE TABLE [dbo].[Teorias] (
    [TituloTeoria] nvarchar(100)  NOT NULL,
    [Documento] varbinary(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Nivel], [Volumen], [TipoEjercicio], [Tema], [NumEjercicio] in table 'Ejercicios'
ALTER TABLE [dbo].[Ejercicios]
ADD CONSTRAINT [PK_Ejercicios]
    PRIMARY KEY CLUSTERED ([Nivel], [Volumen], [TipoEjercicio], [Tema], [NumEjercicio] ASC);
GO

-- Creating primary key on [Nivel], [Volumen], [TipoEjercicio], [Tema] in table 'Lecciones'
ALTER TABLE [dbo].[Lecciones]
ADD CONSTRAINT [PK_Lecciones]
    PRIMARY KEY CLUSTERED ([Nivel], [Volumen], [TipoEjercicio], [Tema] ASC);
GO

-- Creating primary key on [Nivel], [Volumen] in table 'Libros'
ALTER TABLE [dbo].[Libros]
ADD CONSTRAINT [PK_Libros]
    PRIMARY KEY CLUSTERED ([Nivel], [Volumen] ASC);
GO

-- Creating primary key on [Nivel], [Volumen], [TipoEjercicio], [Tema], [NumPagina] in table 'Paginas'
ALTER TABLE [dbo].[Paginas]
ADD CONSTRAINT [PK_Paginas]
    PRIMARY KEY CLUSTERED ([Nivel], [Volumen], [TipoEjercicio], [Tema], [NumPagina] ASC);
GO

-- Creating primary key on [TipoEjercicio] in table 'Secciones'
ALTER TABLE [dbo].[Secciones]
ADD CONSTRAINT [PK_Secciones]
    PRIMARY KEY CLUSTERED ([TipoEjercicio] ASC);
GO

-- Creating primary key on [Nivel], [Volumen], [TipoEjercicio], [Tema], [NumEjercicio], [Fecha] in table 'Resultados'
ALTER TABLE [dbo].[Resultados]
ADD CONSTRAINT [PK_Resultados]
    PRIMARY KEY CLUSTERED ([Nivel], [Volumen], [TipoEjercicio], [Tema], [NumEjercicio], [Fecha] ASC);
GO

-- Creating primary key on [TituloTeoria] in table 'Teorias'
ALTER TABLE [dbo].[Teorias]
ADD CONSTRAINT [PK_Teorias]
    PRIMARY KEY CLUSTERED ([TituloTeoria] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Nivel], [Volumen], [TipoEjercicio], [Tema] in table 'Ejercicios'
ALTER TABLE [dbo].[Ejercicios]
ADD CONSTRAINT [FK_LeccionEjercicio]
    FOREIGN KEY ([Nivel], [Volumen], [TipoEjercicio], [Tema])
    REFERENCES [dbo].[Lecciones]
        ([Nivel], [Volumen], [TipoEjercicio], [Tema])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Nivel], [Volumen], [TipoEjercicio], [Tema] in table 'Paginas'
ALTER TABLE [dbo].[Paginas]
ADD CONSTRAINT [FK_LeccionPagina]
    FOREIGN KEY ([Nivel], [Volumen], [TipoEjercicio], [Tema])
    REFERENCES [dbo].[Lecciones]
        ([Nivel], [Volumen], [TipoEjercicio], [Tema])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Nivel], [Volumen] in table 'Lecciones'
ALTER TABLE [dbo].[Lecciones]
ADD CONSTRAINT [FK_LibroLeccion]
    FOREIGN KEY ([Nivel], [Volumen])
    REFERENCES [dbo].[Libros]
        ([Nivel], [Volumen])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [TipoEjercicio] in table 'Lecciones'
ALTER TABLE [dbo].[Lecciones]
ADD CONSTRAINT [FK_LeccionSeccion]
    FOREIGN KEY ([TipoEjercicio])
    REFERENCES [dbo].[Secciones]
        ([TipoEjercicio])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LeccionSeccion'
CREATE INDEX [IX_FK_LeccionSeccion]
ON [dbo].[Lecciones]
    ([TipoEjercicio]);
GO

-- Creating foreign key on [Nivel], [Volumen], [TipoEjercicio], [Tema], [NumEjercicio] in table 'Resultados'
ALTER TABLE [dbo].[Resultados]
ADD CONSTRAINT [FK_EjercicioResultado]
    FOREIGN KEY ([Nivel], [Volumen], [TipoEjercicio], [Tema], [NumEjercicio])
    REFERENCES [dbo].[Ejercicios]
        ([Nivel], [Volumen], [TipoEjercicio], [Tema], [NumEjercicio])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [TituloTeoria] in table 'Resultados'
ALTER TABLE [dbo].[Resultados]
ADD CONSTRAINT [FK_ResultadoTeoria]
    FOREIGN KEY ([TituloTeoria])
    REFERENCES [dbo].[Teorias]
        ([TituloTeoria])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ResultadoTeoria'
CREATE INDEX [IX_FK_ResultadoTeoria]
ON [dbo].[Resultados]
    ([TituloTeoria]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------